<?php
/**
 * Workflow definition storage handler that saves and loads workflow
 * definitions to and from a database.
 * @author dengdl
 *
 */
class Workflow_DefinitionStorage_Database implements Workflow_DefinitionStorage
{
	/**
     * Zend_Db_Adapter_Abstract instance to be used.
     *
     * @var Zend_Db_Adapter_Abstract
     */
    protected $db;    

    /**
     * Construct a new database definition handler.
     *
     * This constructor is a tie-in.
     *
     * @param Zend_Db_Adapter_Abstract $db
     */
    public function __construct( Zend_Db_Adapter_Abstract $db )
    {
        $this->db = $db;        
    }
    
    /**
     * Load a workflow definition by ID.
     *
     * Providing the name of the workflow that is to be loaded as the
     * optional second parameter saves a database query.
     *
     * @param  int $workflowId
     * @param  string  $workflowName
     * @param  int $workflowVersion
     * @return Workflow_Definition
     * @throws 
     * @throws 
     */
    public function loadById( $workflowId, $workflowName = '', $workflowVersion = 0 )
    {
        // Query the database for the name and version of the workflow.
        $workflow_alias = "";
        $workflow_description = "";
        $workflow_category = "";
        
        if ( empty( $workflowName ) || $workflowVersion == 0 )
        {
        	$result = $this->db->fetchAll("select * from workflow where workflow_id = ?", $workflowId);           

            if ( $result !== false && isset( $result[0] ) )
            {
                $workflowName    = $result[0]['workflow_name'];
                $workflowVersion = $result[0]['workflow_version'];   
                $workflow_alias =  $result[0]['workflow_alias'];  
                $workflow_description =  $result[0]['workflow_description'];
                $workflow_category =  $result[0]['workflow_category_id'];            
            }
            else
            {
                throw new Exception(
                  'Could not load workflow definition.'
                );
            }
        }

        // Query the database for the nodes of the workflow to be loaded.
        $result = $this->db->fetchAll("select node_id, node_class, node_configuration, node_name from node where workflow_id = ?", $workflowId);
        
        $nodes  = array();

        $finallyNode = null;
        $defaultEndNode = null;
        $startNode = null;
        // Create node objects.
        foreach ( $result as $node )
        {
            $configuration = Workflow_DatabaseUtil::unserialize(
              $node['node_configuration'], null
            );

            if ( is_null( $configuration ) )
            {
                $configuration = Workflow_Util::getDefaultConfiguration( $node['node_class'] );
            }

            $nodes[$node['node_id']] = new $node['node_class'](
              $configuration
            );
            
            $nodes[$node['node_id']]->setId($node['node_id']);
            $nodes[$node['node_id']]->setName($node['node_name']);

            if ($nodes[$node['node_id']] instanceof Workflow_Node_Finally &&
                !isset( $finallyNode ) )
            {
                $finallyNode = $nodes[$node['node_id']];
            }

            else if ($nodes[$node['node_id']] instanceof Workflow_Node_End &&
                     !isset( $defaultEndNode ) )
            {
                $defaultEndNode = $nodes[$node['node_id']];
            }

            else if ($nodes[$node['node_id']] instanceof Workflow_Node_Start &&
                     !isset( $startNode ) )
            {
               $startNode = $nodes[$node['node_id']];
            }
        }

        if ( !isset( $startNode ) || !isset( $defaultEndNode ) )
        {
            throw new Exception(
              'Could not load workflow definition.'
            );
        }

        // Connect node objects.
        $connections = $this->db->fetchAll("select
						  node_connection.incoming_node_id as incoming_node_id,
						  node_connection.outgoing_node_id as outgoing_node_id
						from node_connection, node
						where node_connection.incoming_node_id = node.node_id
						and node.workflow_id = ?
						order by node_connection.node_connection_id", $workflowId);       

        foreach ( $connections as $connection )
        {
            $nodes[$connection['incoming_node_id']]->addOutNode(
              $nodes[$connection['outgoing_node_id']]
            );
        }

        if ( !isset( $finallyNode ) ||
             count( $finallyNode->getInNodes() ) > 0 )
        {
            $finallyNode = null;
        }

        // Create workflow object and add the node objects to it.
        $workflow = new Workflow_Definition( $workflowName, $startNode, $defaultEndNode, $finallyNode );
        $workflow->definitionStorage = $this;
        $workflow->id = (int)$workflowId;
        $workflow->version = (int)$workflowVersion;
        
        $workflow->alias = $workflow_alias;
        $workflow->description = $workflow_description;
        $workflow->category = $workflow_category;

        // Query the database for the variable handlers.
        $result = $this->db->fetchAll("select variable, class from variable_handler where workflow_id = ?", $workflowId);
                
        $nodes  = array();

        if ( $result !== false )
        {
            foreach ( $result as $variableHandler )
            {
                $workflow->addVariableHandler(
                  $variableHandler['variable'],
                  $variableHandler['class']
                );
            }
        }

        // Verify the loaded workflow.
        $workflow->verify();

        return $workflow;
    }

    /**
     * Load a workflow definition by name.
     *
     * @param  string  $workflowName
     * @param  int $workflowVersion
     * @return Workflow_Definition
     * @throws 
     * @throws 
     */
    public function loadByName( $workflowName, $workflowVersion = 0 )
    {    

        // Load the current version of the workflow.
        if ( $workflowVersion == 0 )
        {
            $workflowVersion = $this->getCurrentVersionNumber( $workflowName );
        }

        // Query for the workflow_id.
        $result = $this->db->fetchAll("select workflow_id from workflow
        		 where workflow_name = ? and workflow_version = ?", array($workflowName, $workflowVersion));
           

        if ( $result !== false && isset( $result[0] ) )
        {
            return $this->loadById(
              $result[0]['workflow_id'],
              $workflowName,
              $workflowVersion
            );
        }
        else
        {
            throw new Exception(
              'Could not load workflow definition.'
            );
        }
    }

    /**
     * Save a workflow definition to the database.
     *
     * @param  Workflow_Definition $workflow
     * @throws 
     * @throws 
     */
    public function save( Workflow_Definition $workflow )
    {
        // Verify the workflow.
        $workflow->verify();

        $this->db->beginTransaction();

        // Calculate new version number.
        $workflowVersion = $this->getCurrentVersionNumber( $workflow->name ) + 1;

        // Write workflow table row.
        $this->db->insert("workflow", array(
        	'workflow_name' => $workflow->name,
        	'workflow_version' => $workflowVersion,
        	'workflow_created' => time(),
        	'workflow_alias' => $workflow->alias,
        	'workflow_description' => $workflow->description,
        	'workflow_category_id' => $workflow->category
        ));
               
        $workflow->definitionStorage = $this;
        $workflow->id = (int)$this->db->lastInsertId();
        $workflow->version = (int)$workflowVersion;
        
        // Write node table rows.
        $nodeMap = array();

        foreach ( $workflow->nodes as $node )
        {
        	$isFirstUserTaskNode = $workflow->isFirstUserTaskNode($node->getId());
        	$this->db->insert('node', array(
        		'workflow_id' => $workflow->id,
        		'node_id' => $node->getId(),
        		'node_class' => get_class( $node ),
        		'node_configuration' => Workflow_DatabaseUtil::serialize( $node->getConfiguration() ),
        		'node_name' => $node->getName(),
        		'isFirstUserTaskNode' => $isFirstUserTaskNode ? 1 : 0
        	));        	

        	$nodeid = $node->getId();//$this->db->lastInsertId();
            $nodeMap[$nodeid] = $node;            
        }
        
        //workflow_start_candidates table
        if ($workflow->isManualStart())
        {
        	$node = $workflow->startNode->getFirstOutNode();
        	$svc = new Workflow_Service_UserTask();
        	$candidates = $svc->getCandidateUsers($node);
        	
        	foreach ($candidates as $userid)
        	{
        		$this->db->insert('workflow_start_candidates', array(
        			'workflow_id' => $workflow->id,
        			'user_id' => $userid
        		));
        	}
        	
        }           

        // Connect node table rows.
        foreach ( $workflow->nodes as $node )
        {
            foreach ( $node->getOutNodes() as $outNode )
            {
                $incomingNodeId = null;
                $outgoingNodeId = null;

                foreach ( $nodeMap as $_id => $_node )
                {
                    if ( $_node === $node )
                    {
                        $incomingNodeId = $_id;
                    }

                    else if ( $_node === $outNode )
                    {
                        $outgoingNodeId = $_id;
                    }

                    if ( $incomingNodeId !== NULL && $outgoingNodeId !== NULL )
                    {
                        break;
                    }
                }

                $this->db->insert('node_connection', array(
                	'incoming_node_id' => $incomingNodeId,
                	'outgoing_node_id' => $outgoingNodeId
                ));               
            }
        }

        unset( $nodeMap );

        // Write variable handler rows.
        foreach ( $workflow->getVariableHandlers() as $variable => $class )
        {
            $this->db->insert('variable_handler', array(
            	'workflow_id' => $workflow->id,
            	'variable' => $variable,
            	'class' => $class
            ));        	
        }

        $this->db->commit();
    }

    /**
     * Returns the current version number for a given workflow name.
     *
     * @param  string $workflowName
     * @return int
     * @throws 
     */
    protected function getCurrentVersionNumber( $workflowName )
    {
    	$result = $this->db->fetchAll("select workflow_version as version 
    									from workflow where workflow_name = ?", $workflowName);

        if ( $result !== false && isset( $result[0]['version'] ) && $result[0]['version'] !== null )
        {
            return $result[0]['version'];
        }
        else
        {
            return 0;
        }
    }
}
?>